package com.sap.businessone.xapp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.junit.Test;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;

public class DatabaseTest {

    private static final Logger log = LogManager.getLogger(DatabaseTest.class.getName());

    static Connection cn;
    static Statement cs;

    @Test
    public void sqlQuery() {

        cn = getConnection();
        try {
            String sql = "select * from oncp";
            cs = cn.createStatement();
            cs.execute(sql);

            ResultSet rs = cs.getResultSet();

            JsonElement json = toJson(rs);
            log.info(json);

            rs.close();
            cn.close();
        } catch (SQLException e) {
            log.error(e.getMessage());
        }
    }

    private JsonElement toJson(ResultSet rs) throws SQLException {
        JsonArray array = new JsonArray();
        ResultSetMetaData meta = rs.getMetaData();
        int count = meta.getColumnCount();

        while (rs.next()) {
            JsonObject obj = new JsonObject();

            for (int i = 1; i <= count; i++) {
                String column = meta.getColumnLabel(i);
                String value = rs.getString(column);
                obj.addProperty(column, value);
            }
            array.add(obj);
        }

        return array;
    }

    private static Connection getConnection() {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sbodemous", "root", "Initial0");
        } catch (Exception e) {
            log.error(e.getMessage());
        }
        return con;
    }

}
